package db;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;

import extractor.UnneccesaryCharacterHandler;

import aspect.AspectFilter;
import aspect.AspectGenerator;


public class QueryPool {
	Statement stat = null;
	Statement stat1 = null;
	ResultSet rs = null;
	
	public void getSelectTwitter() throws ClassNotFoundException, SQLException {
		Connector.getInstance();
		Connection conn = Connector.getConnection();
		stat = conn.createStatement();
		rs = stat.executeQuery("Select * from rawtweets");
		while ( rs.next() ) {
			//System.out.println(rs.getString(1) + "|" + rs.getString(2));
		}
		conn.close();
	}
	
	public void plusOneCountOfAspects(String aspect) throws ClassNotFoundException, SQLException {
		String result = "";
		Connector.getInstance();
		Connection conn = Connector.getConnection();
		stat = conn.createStatement();
		stat.executeUpdate("update aspects set count = (select max(count) from aspects where noun = '" + aspect.toLowerCase() + "') + 1 where noun ='"+ aspect.toLowerCase() + "';");
		conn.close();
	}	

	public void addNewAspect(String aspect) throws ClassNotFoundException, SQLException {

		String result = "";
		Connector.getInstance();
		Connection conn = Connector.getConnection();
		stat = conn.createStatement();
		stat.executeUpdate("insert into aspects values ('" + aspect.toLowerCase() + "', 1);");
		conn.close();
	}

	public boolean existAspect(String aspect) throws ClassNotFoundException, SQLException {
		boolean result = false;

		Connector.getInstance();
		Connection conn = Connector.getConnection();
		stat = conn.createStatement();
		rs = stat.executeQuery("select * from aspects where noun = '" + aspect.toLowerCase() + "';");

		while ( rs.next() ) {
			if (rs.getString(1)!= null) { result = true; }
		}
		conn.close();
		return result;
	}	

	public String getSelectMaxIDInState(String state, String keyword) throws ClassNotFoundException, SQLException {
		String result = "";
		Connector.getInstance();
		Connection conn = Connector.getConnection();
		stat = conn.createStatement();
		rs = stat.executeQuery("Select max(maxid) from rawtweets where state = '" + state + "' and keyword = '"+ keyword + "';");

		while ( rs.next() ) {
			result = rs.getString(1);
		}
		conn.close();
		return result;
	}	

	public void insertTwitter(String keyword, String time, String maxid, String state, String message) throws ClassNotFoundException, SQLException {	
		Connector.getInstance();
		Connection conn = Connector.getConnection();
		message = message.replaceAll("'","-");
		stat = conn.createStatement();
		//System.out.println("keyword : " + keyword + " state : " + state + " time : " + time + "   message : " + message);
		stat.executeUpdate("insert into rawtweets values ( nextval('serial'), '" + time 
						   + "', '" + keyword + "', '" + maxid + "', '" + state + "', '" + message +  "' );");
		conn.close();
	}

	public void insertFilteredAspects(String temp, String real, int count ) throws ClassNotFoundException, SQLException {	
		Connector.getInstance();
		Connection conn = Connector.getConnection();
		stat = conn.createStatement();
		stat.executeUpdate("insert into filteredaspects values ( '" + temp + "', '" + real + "', " + count + ");");
		conn.close();
	}
	public void deleteFilteredAspects() throws ClassNotFoundException, SQLException {
		Connector.getInstance();
		Connection conn = Connector.getConnection();
		stat = conn.createStatement();
		stat.executeUpdate("delete from filteredaspects;");
		conn.close();
		
	}
	
	
	public void setAspects(String formalMaxID, String keyword) throws ClassNotFoundException, SQLException, IOException {
		String result = "";
		Connector.getInstance();
		Connection conn = Connector.getConnection();
		AspectGenerator aspects = new AspectGenerator();
		stat = conn.createStatement();
		//System.out.println("MaxID = " + formalMaxID);
		if (formalMaxID == null || "".equals(formalMaxID)) {
			rs = stat.executeQuery("Select text from rawtweets where keyword = '" + keyword + "';");
		} else {
			rs = stat.executeQuery("Select text from rawtweets where maxid >= '"+ formalMaxID + "' and keyword = '" + keyword + "';;");
		}
		while ( rs.next() ) {
			result = result + rs.getString(1) + " ";
		}
		
		aspects.aspectGenerator(result);
	}
	
	public void setFilteredAspects() throws ClassNotFoundException, SQLException {
		QueryPool query = new QueryPool();
		Connector.getInstance();
		AspectFilter aspect = new AspectFilter();
		Connection conn = Connector.getConnection();
		stat = conn.createStatement();
		rs = stat.executeQuery("select noun, count from aspects order by count;");
		String temp = "";
		query.deleteFilteredAspects();		
		
		while ( rs.next() ) {
			temp = aspect.isProperAspect(rs.getString(1));
			if (!"".equals(temp)) query.insertFilteredAspects(temp, rs.getString(1), rs.getInt(2));
			
		}
		conn.close();
	}

	public HashMap selectFilteredAspects() throws ClassNotFoundException, SQLException {
		HashMap category = new HashMap();
		Connector.getInstance();
		Connection conn = Connector.getConnection();
		stat = conn.createStatement();
		rs = stat.executeQuery("select category, sum(count) from filteredaspects group by category ;");
		
		if (rs != null) {
			rs.next();
			category.put(rs.getString(1), null);
			//System.out.println(rs.getString(1));
			rs.next();
			category.put(rs.getString(1), null);
			//System.out.println(rs.getString(1));
			rs.next();
			category.put(rs.getString(1), null);
			//System.out.println(rs.getString(1));
			rs.next();
			category.put(rs.getString(1), null);
			//System.out.println(rs.getString(1));
			rs.next();
			category.put(rs.getString(1), null);
			//System.out.println(rs.getString(1));
			rs.next();
			category.put(rs.getString(1), null);
			//System.out.println(rs.getString(1));
			rs.next();
			category.put(rs.getString(1), null);
			//System.out.println(rs.getString(1));			
		}
		conn.close();
		return category;
	}

	public HashMap selectTopFiveAspects(HashMap category) throws SQLException, ClassNotFoundException {
		Connector.getInstance();
		Connection conn = Connector.getConnection();
		ArrayList aspects = new ArrayList();
		String key = "";
		for (Iterator iterator = category.keySet().iterator(); iterator.hasNext();) {
			aspects = new ArrayList();
			key = (String)iterator.next();
			stat = conn.createStatement();
			rs = stat.executeQuery("select aspect from filteredaspects where category ='" + key + "' " + 
					"order by count desc ;");
			while ( rs.next() ) {
				//System.out.println(key + " add : " + rs.getString(1));
				aspects.add(rs.getString(1));
			}
			//System.out.println(key + " " + aspects.size());
			category.put(key, aspects);
		}
		
		conn.close();
		return category;
	}

	public void insertFilteredTweetsByTopFiveAspects(HashMap category) throws ClassNotFoundException, SQLException, InterruptedException {
		Connector.getInstance();
		Connection conn = Connector.getConnection();
		category.keySet();
		
		for (Iterator iterator = category.keySet().iterator(); iterator.hasNext();) {
			String key = (String) iterator.next();
			//System.out.println("key : " + key);
			
			for (int i = 0; i < ((ArrayList)category.get(key)).size(); i++) {
				stat = conn.createStatement();
				stat1 = conn.createStatement();
				//System.out.println(" category : " + key);
				//System.out.println(" value : " + ((ArrayList)category.get(key)).get(i));
				
				rs = stat.executeQuery("select seq, '', time, keyword, state, text from rawtweets where lower(text) like '% " + ((ArrayList)category.get(key)).get(i)+ " %';");
				while ( rs.next() ) {
					//System.out.println("1 : " + rs.getString(1) + " 2 : " + key + " 3 : " + rs.getString(3) + " 4 : " + rs.getString(4) + " 5 : " + rs.getString(5)+ " 6 : " + rs.getString(6));
					try {
						stat1.executeUpdate("insert into sentimenttweets (seq, category, time, keyword, state, text) values " +
								"( " + rs.getInt(1) + ", '" + key + "', '" + rs.getString(3) + "', '" + rs.getString(4)+ "', '" + rs.getString(5)+ "', '" + rs.getString(6)+ "');");					
					} catch (Exception e) {
						System.err.println(e);
					}
				}
			}
		}
		conn.close();
	}		
	
	public ResultSet getSentimentTweets(String fromDate, String toDate) throws ClassNotFoundException, SQLException {
		QueryPool query = new QueryPool();
		Connector.getInstance();
		Connection conn = Connector.getConnection();
		stat = conn.createStatement();
		rs = stat.executeQuery("select a.state, a.category, a.sentiment, count(a.*) as vote, " +
				"(100*count(a.*)/(" +
				"select count(b.*) " +
				"from sentimenttweets b " +
				"where b.state = a.state " +
				"and b.category = a.category " +
				"group by b.state, b.category))||'%' as bo, " +
				"(select count(b.*) " +
				"from sentimenttweets b " +
				"where b.state = a.state " +
				"and b.category = a.category " +
				"group by b.state, b.category) as dodd " +
				"from sentimenttweets a  " +
				"where time between" +
				" '" + fromDate + "' and '" + toDate + "'group by a.state, a.category, a.sentiment order by a.state, dodd desc, a.category, a.sentiment; ");
		conn.close();
		return rs;
	}	

	public ResultSet getTweetsMessage(String state, String sentiment, String category) throws ClassNotFoundException, SQLException {
		QueryPool query = new QueryPool();
		Connector.getInstance();
		Connection conn = Connector.getConnection();
		stat = conn.createStatement();
		rs = stat.executeQuery("select time, text from sentimenttweets " +
				               "where state = '" + state +"' " +
				               "and sentiment = '"+ sentiment + "' " +
				               "and category = '" + category +"' " +
				               "order by time desc;");
		conn.close();
		return rs;
	}	
	public void tweetRefinery() throws ClassNotFoundException, SQLException {	
		Connector.getInstance();
		Connection conn = Connector.getConnection();
		stat = conn.createStatement();
		stat1 = conn.createStatement();
		
		String temp = "";
		UnneccesaryCharacterHandler une = new UnneccesaryCharacterHandler();
		rs = stat.executeQuery("select seq, text from rawtweets;");
		while ( rs.next() ) {
			temp = une.remover(rs.getString(2));
			stat1.executeUpdate("update rawtweets set text = '" + temp + "' where seq =" + rs.getInt(1) + ";");
		}
		
		
		// stat.executeUpdate("insert into filteredaspects values ( '" + temp + "', '" + real + "', " + count + ");");
		conn.close();
	}
}
